package com.kongwg4.all.dao.impl;


import com.kongwg4.all.bean.Customer;
import com.kongwg4.all.dao.CustomerDAO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

/**
 * @author kongwg
 * @create 2021-01-09 22:51
 */
public class CustomerDAOImpl implements CustomerDAO {
    QueryRunner qr = new QueryRunner();

    @Override
    public void insert(Connection conn, Customer cust) throws SQLException {
        String sql = "insert into customers(name,email,birth) values(?,?,?)";
        qr.update(conn, sql, cust.getName(), cust.getEmail(), cust.getBirth());
    }

    @Override
    public void delete(Connection conn, int id) throws SQLException {
        String sql = "delete from customers where id = ?";
        qr.update(conn, sql, id);
    }

    @Override
    public void update(Connection conn, Customer cust) throws SQLException {
        String sql = "update customers set name=?,email=?,birth=? where id =?";
        qr.update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
    }

    @Override
    public Customer getInstance(Connection conn,int id) throws SQLException {
        String sql = "SELECT id,NAME,email,birth FROM customers WHERE id=?";
        BeanHandler<Customer> handler = new BeanHandler<Customer>(Customer.class);
        Customer instance = qr.query(conn, sql, handler, id);
        return instance;
    }

    @Override
    public List<Customer> getAll(Connection conn) throws SQLException {
        String sql = "select id, name, email,birth from customers ";
        BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
        List<Customer> list = qr.query(conn, sql, handler);
        return list;
    }

    @Override
    public long getCount(Connection conn) throws SQLException {
        String sql = "select count(id) from customers";
        ScalarHandler handler = new ScalarHandler();
        Object count = qr.query(conn, sql, handler);
        return (long) count;
    }

    @Override
    public Date getBirth(Connection conn) throws SQLException {
        String sql = "select max(birth) from customers";
        ScalarHandler handler = new ScalarHandler();
        Object maxBirth = qr.query(conn, sql, handler);
        return (Date) maxBirth;
    }
}
